This report documents the annual water-year migration of depth-to-water observations, including ingestion of ZRXP data, QA/QC screening, integration with the active database, and preparation of OVGA-formatted deliverables.
The report summarizes QA/QC checks (record counts, filtered records) and indicator‑well hydrographs, and includes OVGA import procedures in the appendix. For annual updates, adjust the year values for 1) input file paths and the master database, 2) the master output file, and 3) the OVGA upload file. The index.qmd in this repository contains the full processing code and report text.
Data sources and preprocessing
Input files (WY2025):
DepthRP_2024-25.dat — depth to water from reference point (RP) elevation
DepthGE_2024-25.dat — depth to water below ground elevation
OwensValley_DepthWSE_2024-25.dat — water surface elevation
ZRXP format .DAT files
input data
# new data updates -------#.dat ascii files# file_pathWSE <-here('data','hydro','2022-23 Water Year Transfer Packet for ICWD','OwensValley_DepthWSE_2022-23.dat')# file_pathRP <-here('data','hydro','2022-23 Water Year Transfer Packet for ICWD','DepthRP_2022-23.dat')# file_pathGE <-here('data','hydro','2022-23 Water Year Transfer Packet for ICWD','DepthGE_2022-23.dat')file_pathWSE <-here('data','hydro','2024-25 Water Year Transfer Packet for ICWD','OwensValley_DepthWSE_2024-25.dat')file_pathRP <-here('data','hydro','2024-25 Water Year Transfer Packet for ICWD','DepthRP_2024-25.dat')file_pathGE <-here('data','hydro','2024-25 Water Year Transfer Packet for ICWD','DepthGE_2024-25.dat')wYear <-'2025'parse_depth_file <-function(file_path, value_name) { dat_content <-readLines(file_path) data_list <-list() is_data_line <-function(line) {grepl("\\d+ \\d+\\.\\d+", line) }for (line in dat_content) {if (startsWith(line, "#TSPATH")) { staid <-sub('.*/([TVFRSW]\\w+).*', '\\1', line) }if (!startsWith(line, "#") &&is_data_line(line) &&!is.na(staid)) { data_list <-c(data_list, list(data.frame(staid, dateread = line))) } } data_df <-bind_rows(data_list) %>%select(staid, dateread) %>%na.omit() data_df <- data_df %>%separate(dateread, c("date", value_name), sep =" ") data_df[[value_name]] <-as.numeric(data_df[[value_name]]) data_df}depthRP_path <-here('data','hydro','2025','depthRP.RDS')depthGE_path <-here('data','hydro','2025','depthGE.RDS')depthWSE_path <-here('data','hydro','2025','depthWSE.RDS')depthRP <-if (file.exists(depthRP_path)) {readRDS(depthRP_path)} else { depthRP <-parse_depth_file(file_pathRP, "dtw.rp")saveRDS(depthRP, file = depthRP_path) depthRP}depthGE <-if (file.exists(depthGE_path)) {readRDS(depthGE_path)} else { depthGE <-parse_depth_file(file_pathGE, "dtw.bgs")saveRDS(depthGE, file = depthGE_path) depthGE}depthWSE <-if (file.exists(depthWSE_path)) {readRDS(depthWSE_path)} else { depthWSE <-parse_depth_file(file_pathWSE, "wse")saveRDS(depthWSE, file = depthWSE_path) depthWSE}# ground surface elevation at staidsgse_staids <-read_csv(here('data','hydro', 'gse_staids.csv'))# staids in ovga database------# used to cross reference what will be accepted into an import# need to filter out staids that aren't in the ovga list, otherwise import will failovga_points <-read_csv(here("data","hydro","Owens_Monitoring_Points.csv"))# reference point elevation - some staids don't have RP elevations. Identify these here for the current water year transferrpelev <-read_csv(here('data','hydro','rp_elev.csv'))last_ovga_mw <-read_csv(here('output','ovga_uploads_mw_with_rpe_102023_092024_zn.csv'))# historical water levels------hist <-read_csv(here('output','Monitoring_Wells_Master_2024.csv'))hist$date <-ymd(hist$date)
Raw input coverage
File
Records
Unique_staids
Date_start
Date_end
DepthRP_2024-25.dat
82831
743
2024-10-01
2025-09-30
DepthGE_2024-25.dat
74670
655
2024-10-01
2025-09-30
OwensValley_DepthWSE_2024-25.dat
86968
383
2024-10-01
2025-09-30
Each file repeats headers for each monitoring well. Extract the well ID from #TSPATH and join to observation rows (lines that do not start with #).
Monitoring points map (static)
Owens Valley monitoring points (north to south).
parse depthGE.dat
dat_content <-readLines(file_pathGE)# Initialize variables to store datadata_list <-list()# Function to check if a line contains timestamp and valueis_data_line <-function(line) {grepl("\\d+ \\d+\\.\\d+", line)}# Loop through each line in the filefor (line in dat_content) {# Check if the line starts with '#TSPATH'if (startsWith(line, "#TSPATH")) {# Extract 'staid' from the '#TSPATH' line staid <-sub('.*/([TVFRSW]\\w+).*', '\\1', line) }# Check if the line does not start with '#' and contains timestamp and valueif (!startsWith(line, "#") &&is_data_line(line) &&!is.na(staid)) {# If the line does not start with '#' and contains timestamp and value,# add data to the list directly data_list <-c(data_list, list(data.frame(staid, dateread = line))) }}# Combine the data frames in the list into a single data framedata_df <-bind_rows(data_list)# Remove rows with NA values, Remove 'row' and 'value' columnsdepthGE <-na.omit(data_df) %>%select(staid, dateread)#separate the data column and assign numeric class to dtwdepthGE <- depthGE %>%separate(dateread, c("date", "dtw.bgs"),sep =" ") depthGE$dtw.bgs <-as.numeric(depthGE$dtw.bgs)# head(depthGE)saveRDS(depthGE, file =here('data','hydro','2025','depthGE.RDS'))# notes: sub('.*/([TVFRSW]\\w+).*', '\\1', line) extracts the staid from the #TSPATH line.
[1] 74670
[1] 86968
Raw input coverage
Dataset
File
Exists
Records
Unique staids
Date range
DepthRP
DepthRP_2024-25.dat
TRUE
82831
743
2024-10-01 to 2025-09-30
DepthGE
DepthGE_2024-25.dat
TRUE
74670
655
2024-10-01 to 2025-09-30
DepthWSE
OwensValley_DepthWSE_2024-25.dat
TRUE
86968
383
2024-10-01 to 2025-09-30
testwellupdate
# rename the columns# clean up the date with formal date specificationtestwell.up <- try %>%select(-date, -datetime) %>%rename(date = date.y.m.d) %>%mutate(source ="DWP") # head(testwell.up)
append updates to master database
## Append updates to mastertestwells.combined <-bind_rows(hist, testwell.up) # head(testwells.combined)# 1,145,360 records going back to 1971# testwells.combined %>% n_distinct(staid)# testwells.combined %>% distinct(staid) %>% nrow()
## RP can be continually changing so need to be recursive with the RP file for updates.# date conversionrpelev_date <- rpelev %>%mutate(date = lubridate::mdy(date_c))head(rpelev_date) %>%datatable(options =list(pageLength =10, lengthChange =TRUE, scrollX =TRUE))
33 out of 750 staids have multiple measurements per day (some hourly and a few up to 15‑minute intervals). 32 of those 33 staids also have single‑measurement days.
We harmonize these to daily values to reduce data size and make the annual update consistent across measurement frequencies. The next steps below show how we aggregate to daily means and then summarize by water year for export.
43 out of 785 staids have more than 1k records, 9 over 5k each. The rest of the staids have less than 32 measures a year.
2025 water year summary: updated with WY2025 outputs.
assign TR vs ES method based on measurement frequency
# more than 4 reads per month, 48 per year, pressure transducer TR, less electric sounder ES# Number of records from staids with rp elevations# classify method TR v ES by frequency of measurements.# checkrecord.number <- dtwrp.rpelev %>%filter(!is.na(rp_elev)) %>%group_by(staid) %>%summarise(count.with.rpe =n()) %>%mutate(MMethod =case_when(count.with.rpe >=48~"TR", count.with.rpe <48~"ES"))# if there are four reads per day, once per month, approximates to 48. quick and dirty separation. a list of pressure transducer staids would be better if it could be maintained.head(record.number %>%arrange(-count.with.rpe))
# records per staidLA.staids <- dtwrpavg.rpelev %>%group_by(staid) %>%summarise(records =n())# head(LA.staids)# LA.staids %>% arrange(desc(records))#785
staids in data that are also on ovga list
LA.staids.in.ovga <- LA.staids %>%semi_join(ovga_points, by =c("staid"="mon_pt_name"))# LA.staids.in.ovga %>% nrow()#714
monitoring points missing rp elevations after join
# monitoring points missing rp elevations after join# LA.staids.in.ovga# find how many staids are missing rp elevationna.rp.elev <- dtwrpavg.rpelev %>%semi_join(LA.staids.in.ovga, by ='staid') %>%filter(is.na(rp_elev)) %>%group_by(staid) %>%summarise(count.na =n())
anti_join take NA rp elev off
# anti_join removes records in x that match y# Select only data with rp elevations (not na)rpselect <- dtwrpavg.rpelev %>%anti_join(na.rp.elev, by ="staid")# rpselect# 19,793 1.24.2024
Filter to records with RP elevation and valid OVGA monitoring points.
semi_join retain records in ovga staid list
# 6,048 points including monitoring wells, pumping wells, surface water gauging stations.# semi_join returns records in x with a match in yrpselect2 <- rpselect %>%semi_join(ovga_points, by =c("staid"="mon_pt_name"))# head(rpselect2)rpselect2 %>%arrange(staid,desc(date))
datatable( upload,caption ="2024-2025 water year upload formatted for OVGA data management system.",options =list(pageLength =10, lengthChange =TRUE, scrollX =TRUE),class ="compact stripe")
Metric
Value
Rows in OVGA upload
5515
Unique monitoring points
590
Date range
2024-10-01 to 2025-09-30
Results: QA/QC and hydrographs
start of the water year is demarcated for 2022 and 2023
Laws
Hydrographs of indicator wells in the Laws wellfield.
Bishop
Hydrographs of indicator wells in the Bishop wellfield.
Big Pine
Hydrographs of indicator wells in the Big Pine wellfield. T565, and V017GC are in south Big Pine near W218/219.
Taboose Aberdeen
Hydrographs of indicator wells in the Taboose-Aberdeen wellfield.
Thibaut Sawmill
Hydrographs of indicator wells in Thibaut-Sawmill wellfield.
Independence Oak
Hydrographs of indicator wells in Independence-Oak wellfield.
Symmes Shepherd
Hydrographs of indicator wells in Symmes-Shepherd wellfield.
Bairs George
Hydrographs of indicator wells in Bairs George wellfield.
The tables below summarize follow-up QA/QC checks after the hydrograph review.
Monitoring points present in the update but not on the OVGA list
77 monitoring points missing rp elevation and omitted from import
Appendix: GLA Data Depth to Water Import Procedures
confirmed updated 1/23/24 zn
GLA Data Web application. The uploaded Excel Workbook must contain one spreadsheet with 13 columns with the following names in this order:
Field Name
Data Type
Required
WellName
Text
Yes
DateMeasured
Date
Yes
ReportingDate
Date
No
DepthToWater
Numeric
Conditional
ReferencePointElevation
Numeric
Conditional
QAQCLevel
Text
Yes
MeasMethod
Text
Yes
NoMeasFlag
Text
Conditional
QuestMeasFlag
Text
No
DataSource
Text
Yes
CollectedBy
Text
No
UseInReporting
Text
No
Notes
Text
Conditional
WellName The WellName column is required and must contain the name of a monitoring point within the basin selected when the file was uploaded.
DateMeasured The DateMeasured column is required. The field must be a date and can not be in the future nor more than 100 years in the past.
2-1-1 import error says that ReportingDate is not part of the column list ReportingDate The ReportingDate column must be blank or a date. If the field is a date, it must be within 14 days of DateMeasured. If left blank, the column is populated with the value in the DateMeasured column. This field allows users to assign a measurement to an adjacent month for reporting purposes. For example, a measurement collected on May 31st may be intended to be used as an April measurement.
DepthToWater This column must be blank or numeric. DepthToWater is the number of feet from the reference point. If blank, NoMeasFlag is required and ReferencePointElevation must also be blank. Positive values indicate the water level is below the top of the casing, while negative values indicate the water level is above the top of the casing (flowing artesian conditions).
ReferencePointElevation This column must be blank or numeric. ReferencePointElevation is the elevation in feet from where the depth to water measurement took place. If blank, NoMeasFlag is required and DepthToWater must also be blank.
QAQCLevel This field is required and must be one of the following values:
High - Data are of high quality
Medium - Data are inconsistent with previous values or sampling conditions were not ideal. Values will be displayed with a different color on plots.
Low - Data are not considered suitable for display or analysis due to inconsistencies with previous values or poor sampling conditions. Preserves sample in database for record-keeping purposes but not displayed on figures, tables, or used in analyses.
Undecided - QA/QC level has not been determined.
MeasMethod This field is required and must be one of the following values:
Code
Description
ES
Electric sounder measurement
ST
Steel tape measurement
AS
Acoustic or sonic sounder
PG
Airline measurement, pressure gage, or manometer
TR
Electronic pressure transducer
OTH
Other
UNK
Unknown
NoMeasFlag This field must be blank if DepthToWater and ReferencePointElevation contain values. Otherwise, this field is required and must be one of the following values:
Code
Description
0
Measurement discontinued
1
Pumping
2
Pump house locked
3
Tape hung up
4
Can’t get tape in casing
5
Unable to locate well
6
Well has been destroyed
7
Special/other
8
Casing leaking or wet
9
Temporary inaccessible
D
Dry well
F
Flowing artesian
QuestMeasFlag This field must be blank or be one of the following values:
Code
Description
0
Caved or deepened
1
Pumping
2
Nearby pump operating
3
Casing leaking or wet
4
Pumped recently
5
Air or pressure gauge measurement
6
Other
7
Recharge or surface water effects near well
8
Oil or foreign substance in casing
9
Acoustical sounder
E
Recently flowing
F
Flowing
G
Nearby flowing
H
Nearby recently flowing
DataSource This field is required and used to identify where the water level data came from (e.g., entity, database, file, etc.). Limit is 100 characters. default = “LADWP”
CollectedBy This field is optional and used to identify the person that physically collected the data. Limit is 50 characters. default = “LADWP”
UseInReporting This field is optional and used to filter measurements used in reports. If included, the value must be “yes”, “no”, “true”, “false”, “1” or “0”. If blank, a value of “yes” is assumed. default = “yes”
Notes This field must be populated if NoMeasFlag is 7 (special/other) or QuestMeasFlag is 6 (other), otherwise this field is optional. Limit is 255 characters. default = “blank”
Citation
BibTeX citation:
@report{county_water_department2024,
author = {County Water Department, Inyo},
publisher = {Inyo County Water Department},
title = {Depth to Water - Annual Water Year Updates},
date = {2024-12-10},
url = {https://inyo-gov.github.io/hydro-data/},
langid = {en}
}
For attribution, please cite this work as:
County Water Department, Inyo. 2024. “Depth to Water - Annual
Water Year Updates.”Data Report. Inyo County Water
Department. https://inyo-gov.github.io/hydro-data/.
Source Code
---title: "Depth to water - annual water year updates"description: "This report documents the annual water-year migration of depth-to-water observations, including ingestion of ZRXP data, QA/QC screening, integration with the active database, and preparation of OVGA-formatted deliverables."format: html: toc: true toc-depth: 3 anchor-sections: true smooth-scroll: true code-fold: true code-summary: "code" code-line-numbers: true code-overflow: wrap code-link: true html-math-method: katextbl-cap-location: top # number-sections: true author: "Inyo County Water Department"affiliation: "Inyo County Water Department"affiliation-title: "Senior Scientist"date: "2025-10-01"date-modified: "2026-01-26"draft: falseimage: "lw-i85-fac-1.png"citation: type: report container-title: "Data Report" publisher: "Inyo County Water Department" issued: "2024-12-10" url: https://inyo-gov.github.io/hydro-data/google-scholar: truecategories: [R, hydrology, monitoring wells, surface water flow, pumping volumes, LADWP data, OVGA, time series]---# AbstractDepth-to-water observations from LADWP monitoring wells are delivered after each water year. Data are exported from WISKI (water information system by [Kisters](https://www.kisters.net/wiski/)) in ZRXP format, parsed programmatically, [integrated with the active internal database](#mastersave), and [exported in OVGA upload format](#ovgasave).The report summarizes QA/QC checks (record counts, filtered records) and indicator‑well hydrographs, and includes OVGA import procedures in the appendix. For annual updates, adjust the `year` values for 1) [input file paths and the master database](#updatewy), 2) [the master output file](#masterdb), and 3) [the OVGA upload file](#ovgaupdate). The [index.qmd](https://github.com/inyo-gov/hydro-data/blob/main/index.qmd) in this repository contains the full processing code and report text.```{r packages-setup, warning = FALSE,message = FALSE,include=FALSE}#| code-fold: true#| code-summary: "packages"library(tidyverse)library(lubridate)library(readxl)library(knitr)library(here)library(DT)knitr::opts_chunk$set(echo =FALSE,warning =FALSE,message =FALSE,cache=FALSE)```# Data sources and preprocessing {#updatewy}Input files (WY2025):- `DepthRP_2024-25.dat` — depth to water from reference point (RP) elevation- `DepthGE_2024-25.dat` — depth to water below ground elevation- `OwensValley_DepthWSE_2024-25.dat` — water surface elevation{fig-align="center" width="80%"}```{r readdata, echo=TRUE}#| code-fold: true#| code-summary: "input data"# new data updates -------#.dat ascii files# file_pathWSE <-here('data','hydro','2022-23 Water Year Transfer Packet for ICWD','OwensValley_DepthWSE_2022-23.dat')# file_pathRP <-here('data','hydro','2022-23 Water Year Transfer Packet for ICWD','DepthRP_2022-23.dat')# file_pathGE <-here('data','hydro','2022-23 Water Year Transfer Packet for ICWD','DepthGE_2022-23.dat')file_pathWSE <-here('data','hydro','2024-25 Water Year Transfer Packet for ICWD','OwensValley_DepthWSE_2024-25.dat')file_pathRP <-here('data','hydro','2024-25 Water Year Transfer Packet for ICWD','DepthRP_2024-25.dat')file_pathGE <-here('data','hydro','2024-25 Water Year Transfer Packet for ICWD','DepthGE_2024-25.dat')wYear <-'2025'parse_depth_file <-function(file_path, value_name) { dat_content <-readLines(file_path) data_list <-list() is_data_line <-function(line) {grepl("\\d+ \\d+\\.\\d+", line) }for (line in dat_content) {if (startsWith(line, "#TSPATH")) { staid <-sub('.*/([TVFRSW]\\w+).*', '\\1', line) }if (!startsWith(line, "#") &&is_data_line(line) &&!is.na(staid)) { data_list <-c(data_list, list(data.frame(staid, dateread = line))) } } data_df <-bind_rows(data_list) %>%select(staid, dateread) %>%na.omit() data_df <- data_df %>%separate(dateread, c("date", value_name), sep =" ") data_df[[value_name]] <-as.numeric(data_df[[value_name]]) data_df}depthRP_path <-here('data','hydro','2025','depthRP.RDS')depthGE_path <-here('data','hydro','2025','depthGE.RDS')depthWSE_path <-here('data','hydro','2025','depthWSE.RDS')depthRP <-if (file.exists(depthRP_path)) {readRDS(depthRP_path)} else { depthRP <-parse_depth_file(file_pathRP, "dtw.rp")saveRDS(depthRP, file = depthRP_path) depthRP}depthGE <-if (file.exists(depthGE_path)) {readRDS(depthGE_path)} else { depthGE <-parse_depth_file(file_pathGE, "dtw.bgs")saveRDS(depthGE, file = depthGE_path) depthGE}depthWSE <-if (file.exists(depthWSE_path)) {readRDS(depthWSE_path)} else { depthWSE <-parse_depth_file(file_pathWSE, "wse")saveRDS(depthWSE, file = depthWSE_path) depthWSE}# ground surface elevation at staidsgse_staids <-read_csv(here('data','hydro', 'gse_staids.csv'))# staids in ovga database------# used to cross reference what will be accepted into an import# need to filter out staids that aren't in the ovga list, otherwise import will failovga_points <-read_csv(here("data","hydro","Owens_Monitoring_Points.csv"))# reference point elevation - some staids don't have RP elevations. Identify these here for the current water year transferrpelev <-read_csv(here('data','hydro','rp_elev.csv'))last_ovga_mw <-read_csv(here('output','ovga_uploads_mw_with_rpe_102023_092024_zn.csv'))# historical water levels------hist <-read_csv(here('output','Monitoring_Wells_Master_2024.csv'))hist$date <-ymd(hist$date)```## Raw input coverage```{r raw-input-coverage-files}#| code-fold: true#| code-summary: "raw input coverage"raw_input_coverage <-tibble(File =c(basename(file_pathRP),basename(file_pathGE),basename(file_pathWSE) ),Records =c(nrow(depthRP),nrow(depthGE),nrow(depthWSE) ),Unique_staids =c( dplyr::n_distinct(depthRP$staid), dplyr::n_distinct(depthGE$staid), dplyr::n_distinct(depthWSE$staid) ),Date_start =c(min(ymd_hms(depthRP$date), na.rm =TRUE),min(ymd_hms(depthGE$date), na.rm =TRUE),min(ymd_hms(depthWSE$date), na.rm =TRUE) ),Date_end =c(max(ymd_hms(depthRP$date), na.rm =TRUE),max(ymd_hms(depthGE$date), na.rm =TRUE),max(ymd_hms(depthWSE$date), na.rm =TRUE) ))knitr::kable(raw_input_coverage)```{fig-align="center" width="80%" fig-cap="ZRXP .DAT example format from WY2022-23 (illustrative)."}Each file repeats headers for each monitoring well. Extract the well ID from `#TSPATH` and join to observation rows (lines that do not start with `#`).## Monitoring points map (static)```{r monitoring-points-map, fig.width = 6.5, fig.height = 9, fig.cap = "Owens Valley monitoring points (north to south)."}#| code-fold: true#| code-summary: "monitoring points map"ovga_points_map <- ovga_points %>%mutate(mon_pt_lat =as.numeric(mon_pt_lat),mon_pt_long =as.numeric(mon_pt_long),mon_pt_lat =if_else(mon_pt_lat <0, NA_real_, mon_pt_lat),mon_pt_long =if_else(mon_pt_long >0, NA_real_, mon_pt_long) ) %>%filter(!is.na(mon_pt_lat),!is.na(mon_pt_long), hide_from_public ==0,between(mon_pt_lat, 35, 39.5),between(mon_pt_long, -120.5, -116.5) ) %>%arrange(desc(mon_pt_lat))ggplot(ovga_points_map, aes(x = mon_pt_long, y = mon_pt_lat, color = mon_pt_lat)) +geom_point(alpha =0.7, size =0.7) +scale_color_viridis_c(option ="C", guide ="none") +coord_fixed() +labs(x ="Longitude", y ="Latitude") +theme_minimal()``````{r rpdtw, eval=FALSE}#| code-fold: true#| code-summary: "depthRP"dat_content <-readLines(file_pathRP)# Initialize variables to store datadata_list <-list()# Function to check if a line contains timestamp and valueis_data_line <-function(line) {grepl("\\d+ \\d+\\.\\d+", line)}# Loop through each line in the filefor (line in dat_content) {# Check if the line starts with '#TSPATH'if (startsWith(line, "#TSPATH")) {# Extract 'staid' from the '#TSPATH' line staid <-sub('.*/([TVFRSW]\\w+).*', '\\1', line) }# Check if the line does not start with '#' and contains timestamp and valueif (!startsWith(line, "#") &&is_data_line(line) &&!is.na(staid)) {# If the line does not start with '#' and contains timestamp and value,# add data to the list directly data_list <-c(data_list, list(data.frame(staid, dateread = line))) }}# Combine the data frames in the list into a single data framedata_df <-bind_rows(data_list)# Remove rows with NA values, Remove 'row' and 'value' columnsdepthRP <-na.omit(data_df) %>%select(staid, dateread)depthRP <- depthRP %>%separate(dateread, c("date", "dtw.rp"),sep =" ") depthRP$dtw.rp<-as.numeric(depthRP$dtw.rp)head(depthRP)saveRDS(depthRP, file =here('data','hydro','2025','depthRP.RDS'))#count records 2022-23# 96,537 records#count records 2023-24#count recordsnrow(depthRP)``````{r gedtw_ex, echo = TRUE, eval=FALSE}#| code-fold: true#| code-summary: "parse depthGE.dat"dat_content <-readLines(file_pathGE)# Initialize variables to store datadata_list <-list()# Function to check if a line contains timestamp and valueis_data_line <-function(line) {grepl("\\d+ \\d+\\.\\d+", line)}# Loop through each line in the filefor (line in dat_content) {# Check if the line starts with '#TSPATH'if (startsWith(line, "#TSPATH")) {# Extract 'staid' from the '#TSPATH' line staid <-sub('.*/([TVFRSW]\\w+).*', '\\1', line) }# Check if the line does not start with '#' and contains timestamp and valueif (!startsWith(line, "#") &&is_data_line(line) &&!is.na(staid)) {# If the line does not start with '#' and contains timestamp and value,# add data to the list directly data_list <-c(data_list, list(data.frame(staid, dateread = line))) }}# Combine the data frames in the list into a single data framedata_df <-bind_rows(data_list)# Remove rows with NA values, Remove 'row' and 'value' columnsdepthGE <-na.omit(data_df) %>%select(staid, dateread)#separate the data column and assign numeric class to dtwdepthGE <- depthGE %>%separate(dateread, c("date", "dtw.bgs"),sep =" ") depthGE$dtw.bgs <-as.numeric(depthGE$dtw.bgs)# head(depthGE)saveRDS(depthGE, file =here('data','hydro','2025','depthGE.RDS'))# notes: sub('.*/([TVFRSW]\\w+).*', '\\1', line) extracts the staid from the #TSPATH line.``````{r}nrow(depthGE)``````{r wse, eval=FALSE}#| code-fold: true#| code-summary: "depthWSE"dat_content <-readLines(file_pathWSE)# Initialize variables to store datadata_list <-list()# Function to check if a line contains timestamp and valueis_data_line <-function(line) {grepl("\\d+ \\d+\\.\\d+", line)}# Loop through each line in the filefor (line in dat_content) {# Check if the line starts with '#TSPATH'if (startsWith(line, "#TSPATH")) {# Extract 'staid' from the '#TSPATH' line staid <-sub('.*/([TVFRSW]\\w+).*', '\\1', line) }# Check if the line does not start with '#' and contains timestamp and valueif (!startsWith(line, "#") &&is_data_line(line) &&!is.na(staid)) {# If the line does not start with '#' and contains timestamp and value,# add data to the list directly data_list <-c(data_list, list(data.frame(staid, dateread = line))) }}# Combine the data frames in the list into a single data framedata_df <-bind_rows(data_list)# Remove rows with NA values, Remove 'row' and 'value' columnsdepthWSE <-na.omit(data_df) %>%select(staid, dateread)depthWSE <- depthWSE %>%separate(dateread, c("date", "wse"),sep =" ") depthWSE$wse <-as.numeric(depthWSE$wse)# head(depthWSE)saveRDS(depthWSE, file =here('data','hydro','2025','depthWSE.RDS'))``````{r rp}# Print the tidy data frame# head(depthWSE)# 68559 records``````{r}nrow(depthWSE)```## Raw input coverage```{r raw-input-coverage}get_date_range <-function(df, col) {if (is.null(df) ||nrow(df) ==0||!(col %in%names(df))) {return("NA") } parsed <-suppressWarnings(ymd_hms(df[[col]], quiet =TRUE))if (all(is.na(parsed))) { parsed <-suppressWarnings(ymd(df[[col]], quiet =TRUE)) }if (all(is.na(parsed))) {return("NA") }paste0(min(as.Date(parsed), na.rm =TRUE), " to ", max(as.Date(parsed), na.rm =TRUE))}raw_input_coverage <-tibble(Dataset =c("DepthRP", "DepthGE", "DepthWSE"),File =c(basename(file_pathRP), basename(file_pathGE), basename(file_pathWSE)),Exists =c(file.exists(file_pathRP), file.exists(file_pathGE), file.exists(file_pathWSE)),Records =c(nrow(depthRP), nrow(depthGE), nrow(depthWSE)),`Unique staids`=c( dplyr::n_distinct(depthRP$staid), dplyr::n_distinct(depthGE$staid), dplyr::n_distinct(depthWSE$staid) ),`Date range`=c(get_date_range(depthRP, "date"),get_date_range(depthGE, "date"),get_date_range(depthWSE, "date") ))knitr::kable(raw_input_coverage)``````{r jointypes}#| code-fold: true#| code-summary: "fulljoin RP,GE,WSE columns"# bind these three together using a full join so that records are not dropped#bind columns from rp, ge, and wse. use full join to maintain all records if some dates don't have one or the other.try <- depthRP %>%full_join(depthWSE, by =c('staid','date')) %>%full_join(depthGE, by =c('staid','date'))# head(try)``````{r datecolumns}#| code-fold: true#| code-summary: "create date"#| # create different date columns separately for year, month, day of month# use lubridate package to format the datetry$datetime <-ymd_hms(try$date)try<-try %>%mutate(year =year(datetime),month =month(datetime),day =mday(datetime),hour =hour(datetime),minute =minute(datetime),# second = second(datetime),date.y.m.d =make_date(year, month, day))``````{r columnhousekeeping, echo = TRUE}#| code-fold: true#| code-summary: "testwellupdate"#| # rename the columns# clean up the date with formal date specificationtestwell.up <- try %>%select(-date, -datetime) %>%rename(date = date.y.m.d) %>%mutate(source ="DWP") # head(testwell.up)``````{r appendupdates, echo = TRUE}#| code-fold: true#| code-summary: "append updates to master database"## Append updates to mastertestwells.combined <-bind_rows(hist, testwell.up) # head(testwells.combined)# 1,145,360 records going back to 1971# testwells.combined %>% n_distinct(staid)# testwells.combined %>% distinct(staid) %>% nrow()``````{r testwell-summary}#| code-fold: true#| code-summary: "annual update summary"testwell_summary <-tibble(Metric =c("Annual update rows","Annual update staids","Active database rows","Active database staids" ),Value =c(nrow(testwell.up), dplyr::n_distinct(testwell.up$staid),nrow(testwells.combined), dplyr::n_distinct(testwells.combined$staid) )) %>%mutate(Value =format(Value, big.mark =","))knitr::kable(testwell_summary)```# Data integration and outputs {#mastersave}## Database exports {#masterdb}### 2023 water year- [Completed 1-24-24 annual update](https://github.com/inyo-gov/hydro-data/blob/main/output/testwellwy2023.csv)- [Completed 1-24-24 full database](https://github.com/inyo-gov/hydro-data/blob/main/output/Monitoring_Wells_Master_2023.csv)### 2024 water year- [Completed 12-10-24 annual update](https://github.com/inyo-gov/hydro-data/blob/main/output/testwellwy2024.csv)- [Completed 12-10-24 full database](https://github.com/inyo-gov/hydro-data/blob/main/output/Monitoring_Wells_Master_2024.csv)### 2025 water year- [Completed 1-26-26 annual update](https://github.com/inyo-gov/hydro-data/blob/main/output/2025wy/ICWD/testwellwy2025.csv)- [Completed 1-26-26 full database](https://github.com/inyo-gov/hydro-data/blob/main/output/2025wy/ICWD/Monitoring_Wells_Master_2025.csv)```{r dbupdatesavecsv, echo=TRUE}#| code-fold: true#| code-summary: "save master database updates"out_icwd <-here("output", "2025wy", "ICWD")dir.create(out_icwd, recursive =TRUE, showWarnings =FALSE)# single yeartestwell.up %>%write_csv(file.path(out_icwd, "testwellwy2025.csv"))# whole datasettestwells.combined %>%write_csv(file.path(out_icwd, "Monitoring_Wells_Master_2025.csv"))# ```::: column-page```{r updatetable}datatable( testwell.up,caption ="updates to the active database.",options =list(pageLength =10, lengthChange =TRUE, scrollX =TRUE),class ="compact stripe")```:::# Reference point elevation integration```{r make-rpdate, echo = TRUE}#| code-fold: true#| code-summary: "convert RP dates"## RP can be continually changing so need to be recursive with the RP file for updates.# date conversionrpelev_date <- rpelev %>%mutate(date = lubridate::mdy(date_c))head(rpelev_date) %>%datatable(options =list(pageLength =10, lengthChange =TRUE, scrollX =TRUE))``````{r most-recent-rp}#| code-fold: true#| code-summary: "find latest RP elevation updates"# pull the most recent date for each staid's rp elevation measurementmost_recent_rp <- rpelev_date %>%group_by(staid) %>%summarise(date =max(date))head(most_recent_rp) %>%datatable(options =list(pageLength =10, lengthChange =TRUE, scrollX =TRUE))``````{r staid-rpelev}rpelev_4 <- most_recent_rp %>%left_join(rpelev_date, by =c("staid","date")) %>%mutate(latest_rp_date = date) %>%select(-date,-date_c)rpelev_4 %>%arrange(staid) %>%datatable(options =list(pageLength =10, lengthChange =TRUE, scrollX =TRUE))# head(rpelev_4)# 1178 rp elevations 1363 unique staids``````{r rpelev-join-update}#| code-fold: true#| code-summary: "join rp to annual update"dtwrp.rpelev <- testwell.up %>%select(staid,date,dtw.rp) %>%left_join(rpelev_4, by ='staid')#123,311 1-24-24dtwrp.rpelev%>%summarise(count_unique_staids =n_distinct(staid))# str(dtwrpavg.rpelev)# datatable(dtwrp.rpelev,# caption = 'most recent water year with rp elevation joined.')``````{r staidDailyCount}#| code-fold: true#| code-summary: "staid daily count"# top staids by daily countdailycount <- dtwrp.rpelev %>%group_by(staid,date) %>%summarise(daily_count =n()) %>%arrange(-daily_count)dailycount %>%datatable(options =list(pageLength =10, lengthChange =TRUE, scrollX =TRUE))``````{r dailyhist}#| code-fold: true#| code-summary: "daily count histogram"# histogram of daily countsdailycount %>%ggplot(aes(x = daily_count))+geom_histogram()``````{r binStaidsByDailyFreq}#| code-fold: true#| code-summary: "join rp to annual update"# bin staids by daily countdc <- dailycount%>%mutate(daily_count_category =cut(daily_count, breaks =c(0,1,2,3,4, 5, 10, 15, 20,50,100), include.lowest =TRUE)) dc# %>% n_distinct(staid)``````{r staidsPerBin}bincount <- dc %>%group_by(daily_count_category) %>%summarise(count_unique_staids =n_distinct(staid))bincount``````{r }# from staids that have multirecord days, how many also have singleton record days?single <- bincount$daily_count_category[1]%>%as.data.frame()single[1]# bincount %>% # filter(!daily_count_category == single[1]) %>%# summarise(tot_multday_staids = sum(count_unique_staids))``````{r daily-summary-text, echo=FALSE, results="asis"}# Summarize multiple-measurement wells for text outputstaids_total <- dailycount %>%distinct(staid) %>%nrow()staids_multi <- dailycount %>%filter(daily_count >1) %>%distinct(staid) %>%nrow()staids_multi_with_single <- dailycount %>%group_by(staid) %>%summarise(has_multi =any(daily_count >1), has_single =any(daily_count ==1), .groups ="drop") %>%filter(has_multi, has_single) %>%nrow()cat(paste0( staids_multi, " out of ", staids_total," staids have multiple measurements per day (some hourly and a few up to 15‑minute intervals). " ))cat(paste0( staids_multi_with_single, " of those ", staids_multi, " staids also have single‑measurement days." ))```We harmonize these to daily values to reduce data size and make the annual update consistent across measurement frequencies. The next steps below show how we aggregate to daily means and then summarize by water year for export.```{r}wyfreq <- dc %>%group_by(staid, daily_count_category) %>%summarise(wy_totcount =sum(daily_count)) %>%arrange(-wy_totcount) %>%mutate(wy_count_category =cut(wy_totcount, breaks =c(0, 5, 10, 15, 20,50,1000,5000,10000), include.lowest =TRUE))wyfreq %>%datatable(options =list(pageLength =10, lengthChange =TRUE, scrollX =TRUE))```43 out of 785 staids have more than 1k records, 9 over 5k each. The rest of the staids have less than 32 measures a year.2025 water year summary: updated with WY2025 outputs.```{r}# x = as.factor(staid),wyfreq %>%ggplot(aes( x = wy_totcount, fill = wy_count_category))+geom_histogram()+# +color = "white", fill = "lightblue", alpha = 0.7# geom_histogram(breaks = c(0,1,2,3,4, 5,6,7,8,9, 10,11,12, 15, 20,50,1000,5000,10000)) +xlim(40,6000)+ylim(0, 5)+theme_linedraw()+ylab("Number of wells")+xlab("Number of records in water year (wells with more than 40 records")# breaks = c(0,1,2,3,4, 5, 10, 15, 20,50,100)# scale_x_continuous(breaks = seq(0, 35, by = 1))``````{r}# x = as.factor(staid),wyfreq %>%ggplot(aes( x = wy_totcount, fill = wy_count_category))+geom_histogram()+# +color = "white", fill = "lightblue", alpha = 0.7# geom_histogram(breaks = c(0,1,2,3,4, 5,6,7,8,9, 10,11,12, 15, 20,50,1000,5000,10000)) +xlim(0,35)+theme_linedraw()+ylab("Number of wells")+xlab("Number of records in water year")# breaks = c(0,1,2,3,4, 5, 10, 15, 20,50,100)# scale_x_continuous(breaks = seq(0, 35, by = 1))``````{r}# x = as.factor(staid),wyfreq %>%ggplot(aes( x = wy_totcount, fill = wy_count_category))+geom_histogram()+# +color = "white", fill = "lightblue", alpha = 0.7# geom_histogram(breaks = c(0,1,2,3,4, 5,6,7,8,9, 10,11,12, 15, 20,50,1000,5000,10000)) +# xlim(0,35)+theme_linedraw()+ylab("Number of wells")+xlab("Number of records in water year")# breaks = c(0,1,2,3,4, 5, 10, 15, 20,50,100)# scale_x_continuous(breaks = seq(0, 35, by = 1))``````{r avgdailydtw, echo = TRUE}#| code-fold: true#| code-summary: "avg daily dtw - aggregating"# daily average dtwdtwrpavg.rpelev <- dtwrp.rpelev %>%group_by(staid, date, rp_elev) %>%summarise(dtw.rp =round(mean(dtw.rp),2))#19,865 1-24-24# dtwrpavg.rpelevBeta <- dtwrp.rpelev %>% group_by(staid, date, rp_elev) %>% summarise(dtw.rp = round(mean(dtw.rp),2),# dailyCount = n()# ) %>% arrange(desc(dailyCount))dtwrpavg.rpelev# datatable(dtwrpavg.rpelev,# caption = 'daily averaging reduces data')``````{r esvtr, echo = TRUE}#| code-fold: true#| code-summary: "assign TR vs ES method based on measurement frequency"# more than 4 reads per month, 48 per year, pressure transducer TR, less electric sounder ES# Number of records from staids with rp elevations# classify method TR v ES by frequency of measurements.# checkrecord.number <- dtwrp.rpelev %>%filter(!is.na(rp_elev)) %>%group_by(staid) %>%summarise(count.with.rpe =n()) %>%mutate(MMethod =case_when(count.with.rpe >=48~"TR", count.with.rpe <48~"ES"))# if there are four reads per day, once per month, approximates to 48. quick and dirty separation. a list of pressure transducer staids would be better if it could be maintained.head(record.number %>%arrange(-count.with.rpe))``````{r annualrecords, echo = TRUE}#| code-fold: true#| code-summary: "number of records per staid"# records per staidLA.staids <- dtwrpavg.rpelev %>%group_by(staid) %>%summarise(records =n())# head(LA.staids)# LA.staids %>% arrange(desc(records))#785``````{r inovga, echo = TRUE}#| code-fold: true#| code-summary: "staids in data that are also on ovga list"#| LA.staids.in.ovga <- LA.staids %>%semi_join(ovga_points, by =c("staid"="mon_pt_name"))# LA.staids.in.ovga %>% nrow()#714``````{r narpelev, echo = TRUE}#| code-fold: true#| code-summary: "monitoring points missing rp elevations after join"# monitoring points missing rp elevations after join# LA.staids.in.ovga# find how many staids are missing rp elevationna.rp.elev <- dtwrpavg.rpelev %>%semi_join(LA.staids.in.ovga, by ='staid') %>%filter(is.na(rp_elev)) %>%group_by(staid) %>%summarise(count.na =n())``````{r datawithrp, echo = TRUE}#| code-fold: true#| code-summary: "anti_join take NA rp elev off"# anti_join removes records in x that match y# Select only data with rp elevations (not na)rpselect <- dtwrpavg.rpelev %>%anti_join(na.rp.elev, by ="staid")# rpselect# 19,793 1.24.2024```Filter to records with RP elevation and valid OVGA monitoring points.```{r withrpovgalist, echo = TRUE}#| code-fold: true#| code-summary: "semi_join retain records in ovga staid list"# 6,048 points including monitoring wells, pumping wells, surface water gauging stations.# semi_join returns records in x with a match in yrpselect2 <- rpselect %>%semi_join(ovga_points, by =c("staid"="mon_pt_name"))# head(rpselect2)rpselect2 %>%arrange(staid,desc(date))# datatable(rpselect2)```## OVGA template and export {#ovgasave}```{r ovgacolumns, echo = TRUE}#| code-fold: true#| code-summary: "create ovga template"# methodinfer %>% distinct()methodinfer <- record.number %>%select(-count.with.rpe)# %>% distinct(staid)upload <- rpselect2 %>%left_join(methodinfer, by ="staid") %>%# select(-latest_rp_date) %>% select(WellName = staid, DateMeasured = date, DepthToWater = dtw.rp, ReferencePointElevation = rp_elev , MMethod ) %>%mutate(ReportingDate ="",# ExclusionCondition = "" ,QAQCLevel ="High",MeasMethod = MMethod,#"ES",# from join aboveNoMeasFlag ="",QuestMeasFlag ="",DataSource ="LADWP",CollectedBy ="LADWP",UseInReporting ="yes",Notes ="") %>%select(-MMethod)%>%filter(DepthToWater <500&!is.na(DepthToWater) & DepthToWater !='NA'& DepthToWater !=-777& ReferencePointElevation !=0) %>%relocate(ReportingDate, .after = DateMeasured)upload ``````{r}df <- upload %>%filter(ReferencePointElevation =="")```# 2023 water year[Completed 1-24-24](https://github.com/inyo-gov/hydro-data/blob/main/output/ovga_uploads_mw_with_rpe_102022_092023_zn.csv)[csv](https://raw.githubusercontent.com/inyo-gov/hydro-data/main/output/ovga_uploads_mw_with_rpe_102022_092023_zn.csv)# 2024 water year[Completed 12-10-24](https://github.com/inyo-gov/hydro-data/blob/main/output/ovga_uploads_mw_with_rpe_102023_092024_zn.csv)[csv](https://raw.githubusercontent.com/inyo-gov/hydro-data/main/output/ovga_uploads_mw_with_rpe_102023_092024_zn.csv)# 2025 water year[Completed 1-26-26](https://github.com/inyo-gov/hydro-data/blob/main/output/2025wy/OVGA/ovga_uploads_mw_with_rpe_102024_092025_zn.csv)[csv](https://raw.githubusercontent.com/inyo-gov/hydro-data/main/output/2025wy/OVGA/ovga_uploads_mw_with_rpe_102024_092025_zn.csv)- `r rpselect %>% nrow()` well-days in update with rp elevations- `r rpselect %>% group_by(staid) %>% summarise(records = n()) %>% nrow()` staids in update with rp elevations- `r rpselect2 %>% nrow()` well-days in update with rp elevations and in ovga list- `r rpselect2 %>% group_by(staid) %>% summarise(records = n()) %>% nrow()` staids in update with rp elevations and in ovga list- `r upload %>% nrow()` well-days in the ovga upload- `r upload %>% group_by(WellName) %>% summarise(records = n()) %>% nrow()` staids in the ovga upload## OVGA export and comparison {#ovgaupdate}```{r}#| code-fold: true#| code-summary: "last vs current staid list"# last year's export file for comparison in staids etclast <- last_ovga_mw %>%group_by(WellName) %>%summarise(last.staid = WellName[1])%>%ungroup() current <- upload %>%group_by(WellName) %>%summarise(current.staid = WellName[1]) %>%ungroup() compare <- last %>%full_join(current, by ="WellName") %>%filter(is.na(current.staid))datatable( compare,options =list(pageLength =10, lengthChange =TRUE, scrollX =TRUE),class ="compact stripe")``````{r, eval = FALSE}#| code-fold: true#| code-summary: "write comparison to output"#| compare %>%write_csv(here("output","staidsMissingDepthGEandRP.csv"))```Review this comparison after WY2025 processing to confirm which staids are missing and whether WSE-derived RP calculations are needed.```{r}#| code-fold: true#| code-summary: "year over year difference"# check if 35 staids not in the ovga upload are in the initial data?yoymisscheck <- testwell.up %>%filter(staid %in% compare$WellName)# 17479 records in these 35 staids# these have depthWSE but not depthGEyoymisscheck %>%glimpse()``````{r ovga_format_saved, echo=TRUE}#| eval: false#| code-fold: true#| code-summary: "save OVGA import"out_ovga <-here("output", "2025wy", "OVGA")dir.create(out_ovga, recursive =TRUE, showWarnings =FALSE)upload %>%write_csv(file.path(out_ovga, "ovga_uploads_mw_with_rpe_102024_092025_zn.csv"))```::: column-page```{r printovga, echo = TRUE}#| code-fold: true#| code-summary: "datatable"datatable( upload,caption ="2024-2025 water year upload formatted for OVGA data management system.",options =list(pageLength =10, lengthChange =TRUE, scrollX =TRUE),class ="compact stripe")```:::```{r ovga-import-metrics}#| code-fold: true#| code-summary: "OVGA import metrics"ovga_import_metrics <-tibble(Metric =c("Rows in OVGA upload","Unique monitoring points","Date range" ),Value =c(nrow(upload), dplyr::n_distinct(upload$WellName),paste0(min(upload$DateMeasured, na.rm =TRUE), " to ", max(upload$DateMeasured, na.rm =TRUE)) )) %>%mutate(Value =format(Value, big.mark =","))knitr::kable(ovga_import_metrics)```# Results: QA/QC and hydrographs {#qaqc}start of the water year is demarcated for 2022 and 2023## Laws```{r lwiwells,fig.cap='Hydrographs of indicator wells in the Laws wellfield.'}mo.dtws <- testwells.combinedwyStart <-as.Date('2024-10-01')wyPrev <-as.Date('2023-10-01')# Laws setstaid.set <-c('T107','T434','T436',# 'T438','T490','T492','T795','V001G','T574')mo.dtws %>%filter(staid %in% staid.set, dtw.bgs <40, year >2015) %>%ggplot(aes(x = date, y = dtw.bgs, color = staid))+geom_line()+geom_point()+scale_y_reverse()+xlab('Date')+ylab('DTW (feet below ground surface)')+geom_vline(xintercept = wyStart)+geom_vline(xintercept = wyPrev)```## Bishop```{r bisiwells,fig.cap='Hydrographs of indicator wells in the Bishop wellfield.'}# northern bishopstaid.set <-c('T108','T384',# 'T498','T485',#east bishop# 'T497','T501','T391'# ,#west bishop# 'T108',# 'T390',# 'T387',# 'T389')mo.dtws %>%filter(dtw.bgs<40,staid %in% staid.set, year>2015) %>%ggplot(aes(x = date, y = dtw.bgs, color = staid))+geom_line()+geom_point()+scale_y_reverse()+xlab('Date')+ylab('DTW (feet below ground surface)')+geom_vline(xintercept = wyStart)+geom_vline(xintercept = wyPrev)```## Big Pine```{r bpiwells,fig.cap='Hydrographs of indicator wells in the Big Pine wellfield. T565, and V017GC are in south Big Pine near W218/219.'}# BP setstaid.set <-c('T425','T426','T469','T572','T798','T799','T567','T800','T565','V017GC')mo.dtws %>%filter(staid %in% staid.set, dtw.bgs <40, year >2015) %>%ggplot(aes(x = date, y = dtw.bgs, color = staid))+geom_line()+geom_point()+scale_y_reverse()+xlab('Date')+ylab('DTW (feet below ground surface)')+geom_vline(xintercept = wyStart)+geom_vline(xintercept = wyPrev)```## Taboose Aberdeen```{r taiwells,fig.cap='Hydrographs of indicator wells in the Taboose-Aberdeen wellfield.'}# ta setstaid.set <-c('T417','T418','T419','T421','T502','T504','T505','T586','T587')mo.dtws %>%filter(staid %in% staid.set, year >2015) %>%ggplot(aes(x = date, y = dtw.bgs, color = staid))+geom_line()+geom_point()+scale_y_reverse()+xlab('Date')+ylab('DTW (feet below ground surface)')+geom_vline(xintercept = wyStart)+geom_vline(xintercept = wyPrev)```## Thibaut Sawmill```{r tsiwells,fig.cap='Hydrographs of indicator wells in Thibaut-Sawmill wellfield.'}# BP setstaid.set <-c('T413','T414','T415','T507','T587')mo.dtws %>%filter(staid %in% staid.set, year >2015) %>%ggplot(aes(x = date, y = dtw.bgs, color = staid))+geom_line()+geom_point()+scale_y_reverse()+xlab('Date')+ylab('DTW (feet below ground surface)')+geom_vline(xintercept = wyStart)+geom_vline(xintercept = wyPrev)```## Independence Oak```{r ioiwells,fig.cap='Hydrographs of indicator wells in Independence-Oak wellfield.'}# IO setstaid.set <-c('T406','T407','T408',# 'T409','T412',# 'T453','T546','T809')mo.dtws %>%filter(staid %in% staid.set, year >2015) %>%ggplot(aes(x = date, y = dtw.bgs, color = staid))+geom_line()+geom_point()+scale_y_reverse()+xlab('Date')+ylab('DTW (feet below ground surface)')+geom_vline(xintercept = wyStart)+geom_vline(xintercept = wyPrev)```## Symmes Shepherd```{r ssiwells,fig.cap='Hydrographs of indicator wells in Symmes-Shepherd wellfield.'}# ss setstaid.set <-c('T402','T403','T404','V009G','T510','T511','T447')mo.dtws %>%filter(staid %in% staid.set, year >2015) %>%ggplot(aes(x = date, y = dtw.bgs, color = staid))+geom_line()+geom_point()+scale_y_reverse()+xlab('Date')+ylab('DTW (feet below ground surface)')+geom_vline(xintercept = wyStart)+geom_vline(xintercept = wyPrev)```## Bairs George```{r BGiwellsaqbuff,fig.cap='Hydrographs of indicator wells in Bairs George wellfield.'}staid.set <-c('T398',#near aq'T400',#east of aq'T597','T598',#near pumping - need don't have in latest 'T596')#near aqmo.dtws %>%filter(staid %in% staid.set, year >2015,dtw.bgs<60) %>%ggplot(aes(x = date, y = dtw.bgs, color = staid))+geom_line()+geom_point()+scale_y_reverse()+xlab('Date')+ylab('DTW (feet below ground surface)')+geom_vline(xintercept = wyStart)+geom_vline(xintercept = wyPrev)```The tables below summarize follow-up QA/QC checks after the hydrograph review.**Monitoring points present in the update but not on the OVGA list**```{r staidsnotinovga}LA.staids.notin.ovga <- LA.staids %>%anti_join(ovga_points, by =c("staid"="mon_pt_name"))head(LA.staids.notin.ovga)#105```**Points missing RP elevations where GSE is missing**```{r needgse}need.gse <- na.rp.elev %>%anti_join(gse_staids, by ='staid')head(need.gse)```**Points missing RP elevations where GSE exists**```{r havegse}have.gse <- na.rp.elev %>%semi_join(gse_staids, by ='staid')head(have.gse)# 6 staids with gse that can be joined - 1/24/24 zn```**Missing-RP points that are not on the OVGA list**```{r notin-ovga}not_in_ovga <- need.gse %>%anti_join(ovga_points, by =c("staid"="mon_pt_name"))head(not_in_ovga)# 0 not in ovga out of 168 staids 142 of these not in ovga```**Missing-RP points that are on the OVGA list**```{r inovga-gse}in_ovga_gse <- need.gse %>%semi_join(ovga_points, by =c("staid"="mon_pt_name"))head(in_ovga_gse)# 0 in ovga that need gse joins 26 in ovga that need gse joinsin_ovga_gse```**Total record count missing RP elevations**```{r rp-missing}# total records from monitoring points missing rp elevations# total recordsna.rp.elev.tot <- na.rp.elev %>%summarise(count.na =sum(count.na))# 453,119 records without rp.elev. with pt data# 36517 aggregated to dailyhead(na.rp.elev.tot)```**Sample records from points missing RP elevations**```{r pointsmissing-rp}# filter records in the set corresponding to monitoring points without rp elevationrpcheck <- dtwrpavg.rpelev %>%semi_join(na.rp.elev, by ="staid")head(rpcheck)# 453,119 from 278 staids have no rp elevations# 36,517 aggregated to daily# we can filter out these staids for now, save the list and when rp elevations are updated we can add# the wells within OVGA area. 278 unique(rpcheck$staid)```::: column-page- `r na.rp.elev %>% nrow()` monitoring points missing rp elevation and omitted from import```{r missdt}#| code-fold: true#| code-summary: "table"datatable(na.rp.elev,caption ='Missing RP elevations - follow up.',options =list(pageLength =10, lengthChange =TRUE, scrollX =TRUE))# 1/23/24 zn - only 16 wells missing rp elevations in this year's 2023 wy set# December 10, 2024. 2024 wy - 83 missing rp elevations```:::```{r ecdf_plot, eval=FALSE}dtwrp.rpelev %>%group_by(staid)%>%summarise(annfreq=n()) %>%ggplot(aes(x = annfreq))+stat_ecdf()+# +# geom_histogram()+ylim(.5,1)+xlim(0,50)+geom_vline(xintercept=20, color="blue")+geom_hline(yintercept=.95,color='red')# - 96% of staids have one read per month.# 87% in 2024```# Appendix: GLA Data Depth to Water Import Proceduresconfirmed updated 1/23/24 zn[GLA Data Web application](https://owens.gladata.com/). The uploaded Excel Workbook must contain one spreadsheet with 13 columns with the following names in this order:|||||-------------------------|---------------|--------------|| **Field Name** | **Data Type** | **Required** || WellName | Text | Yes || DateMeasured | Date | Yes || ReportingDate | Date | No || DepthToWater | Numeric | Conditional || ReferencePointElevation | Numeric | Conditional || QAQCLevel | Text | Yes || MeasMethod | Text | Yes || NoMeasFlag | Text | Conditional || QuestMeasFlag | Text | No || DataSource | Text | Yes || CollectedBy | Text | No || UseInReporting | Text | No || Notes | Text | Conditional |*WellName* The WellName column is required and must contain the name of a monitoring point within the basin selected when the file was uploaded.*DateMeasured* The DateMeasured column is required. The field must be a date and can not be in the future nor more than 100 years in the past.2-1-1 import error says that ReportingDate is not part of the column list *ReportingDate* The ReportingDate column must be blank or a date. If the field is a date, it must be within 14 days of DateMeasured. *If left blank, the column is populated with the value in the DateMeasured column*. This field allows users to assign a measurement to an adjacent month for reporting purposes. For example, a measurement collected on May 31st may be intended to be used as an April measurement.*DepthToWater* This column must be blank or numeric. DepthToWater is the number of feet from the reference point. If blank, NoMeasFlag is required and ReferencePointElevation must also be blank. Positive values indicate the water level is below the top of the casing, while negative values indicate the water level is above the top of the casing (flowing artesian conditions).*ReferencePointElevation* This column must be blank or numeric. ReferencePointElevation is the elevation in feet from where the depth to water measurement took place. If blank, NoMeasFlag is required and DepthToWater must also be blank.*QAQCLevel* This field is required and must be one of the following values:- High - Data are of high quality- Medium - Data are inconsistent with previous values or sampling conditions were not ideal. Values will be displayed with a different color on plots.- Low - Data are not considered suitable for display or analysis due to inconsistencies with previous values or poor sampling conditions. Preserves sample in database for record-keeping purposes but not displayed on figures, tables, or used in analyses.- Undecided - QA/QC level has not been determined.*MeasMethod* This field is required and must be one of the following values:||||------|--------------------------------------------------|| Code | Description || ES | Electric sounder measurement || ST | Steel tape measurement || AS | Acoustic or sonic sounder || PG | Airline measurement, pressure gage, or manometer || TR | Electronic pressure transducer || OTH | Other || UNK | Unknown |*NoMeasFlag* This field must be blank if DepthToWater and ReferencePointElevation contain values. Otherwise, this field is required and must be one of the following values:||||------|--------------------------|| Code | Description || 0 | Measurement discontinued || 1 | Pumping || 2 | Pump house locked || 3 | Tape hung up || 4 | Can't get tape in casing || 5 | Unable to locate well || 6 | Well has been destroyed || 7 | Special/other || 8 | Casing leaking or wet || 9 | Temporary inaccessible || D | Dry well || F | Flowing artesian |*QuestMeasFlag* This field must be blank or be one of the following values:||||------|---------------------------------------------|| Code | Description || 0 | Caved or deepened || 1 | Pumping || 2 | Nearby pump operating || 3 | Casing leaking or wet || 4 | Pumped recently || 5 | Air or pressure gauge measurement || 6 | Other || 7 | Recharge or surface water effects near well || 8 | Oil or foreign substance in casing || 9 | Acoustical sounder || E | Recently flowing || F | Flowing || G | Nearby flowing || H | Nearby recently flowing |*DataSource* This field is **required** and used to identify where the water level data came from (e.g., entity, database, file, etc.). Limit is 100 characters. default = "LADWP"*CollectedBy* This field is optional and used to identify the person that physically collected the data. Limit is 50 characters. default = "LADWP"*UseInReporting* This field is optional and used to filter measurements used in reports. If included, the value must be "yes", "no", "true", "false", "1" or "0". If blank, a value of "yes" is assumed. default = "yes"*Notes* This field must be populated if NoMeasFlag is 7 (special/other) or QuestMeasFlag is 6 (other), otherwise this field is optional. Limit is 255 characters. default = "blank"